{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "from datetime import timedelta \n",
    "import import_ipynb\n",
    "from utils_common import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data of COVID-19\n",
    "# source: DXY\n",
    "_DXY_DATA_FILE_ = 'https://raw.githubusercontent.com/BlankerL/DXY-2019-nCoV-Data/master/csv/DXYArea.csv'\n",
    "# source: JHU\n",
    "_JHU_DATA_PATH_ = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/'\n",
    "_JHU_DATA_START_DATE = '2020-01-22'\n",
    "# source: Kaggle\n",
    "_Kaggle_DATA_PATH_ = './data/data_kaggle.csv'\n",
    "\n",
    "_Figure_PATH_ = './figures/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some English names for provinces are not correct and need to be revised first\n",
    "#chn_en = pd.read_csv(_CHN_EN_DICT_, encoding='utf-8')\n",
    "#chn_en.loc[chn_en.Chinese == '贵州省','English'] = 'Guizhou'\n",
    "#chn_en.loc[chn_en.Chinese == '海南省','English'] = 'Hainan'\n",
    "#chn_en.loc[chn_en.Chinese == '内蒙古自治区','English'] = 'Inner Mongolia'\n",
    "#chn_en.loc[chn_en.Chinese == '陕西省','English'] = 'Shaanxi'\n",
    "#chn_en.to_csv('./data/locationDict.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rename_cities(snapshots):\n",
    "    '''\n",
    "    For now, \n",
    "    1. entries will be ignored if city_name == xxx(xxx), and xxx is already in the city_name set.\n",
    "    2. entries will be renamed if city_name == xxx, where xxxy is already in the set with y being an administrative subdivision such as 县 and 区.\n",
    "    '''\n",
    "    \n",
    "    dup_frm = snapshots[snapshots['city_name'].str.contains('（', na = False)]\n",
    "    rename_dict = {}\n",
    "    if len(dup_frm) >= 0:\n",
    "        rename_dict = dict([(name, name.split('（')[0]) for name in dup_frm['city_name']])\n",
    "    \n",
    "    ###### 北京 Beijing\n",
    "    rename_dict['大兴'] = '大兴区' # + 2020-01-31\n",
    "    rename_dict['怀柔'] = '怀柔区' # + \n",
    "    rename_dict['昌平'] = '昌平区' # + \n",
    "    rename_dict['海淀'] = '海淀区' # +\n",
    "    rename_dict['石景山'] = '石景山区'\n",
    "    rename_dict['西城'] = '西城区'\n",
    "    rename_dict['顺义'] = '顺义区'\n",
    "    rename_dict['东城'] = '东城区'\n",
    "    rename_dict['丰台'] = '丰台区'\n",
    "    rename_dict['通州'] = '通州区'\n",
    "    rename_dict['门头沟'] = '门头沟区'\n",
    "    rename_dict['武汉来京人员'] = '外地来京人员' # + 2020-01-24\n",
    "    #rename_dict['朝阳'] = '朝阳区' # + only for 北京市，not for 辽宁省\n",
    "    \n",
    "    ###### 重庆 Chongqing\n",
    "    rename_dict['云阳'] = '云阳县' # * 2020-01-31 2020-01-24\n",
    "    rename_dict['巫山'] = '巫山县' # * 2020-01-31 2020-01-24\n",
    "    rename_dict['巫溪'] = '巫溪县' # * 2020-01-31 2020-01-24\n",
    "    rename_dict['丰都'] = '丰都县'\n",
    "    rename_dict['垫江'] = '垫江县'\n",
    "    rename_dict['城口'] = '城口县'\n",
    "    rename_dict['奉节'] = '奉节县'\n",
    "    rename_dict['石柱'] = '石柱县'\n",
    "    rename_dict['秀山'] = '秀山县'\n",
    "    rename_dict['酉阳'] = '酉阳县'\n",
    "\n",
    "    ###### 河南 Henan \n",
    "    ###### 邓州，永城，滑县，长垣县 to be reomved\n",
    "    rename_dict['安阳市'] = '安阳' # + 2020-01-28 2020-02-01\n",
    "    rename_dict['漯河市'] = '漯河'\n",
    "    rename_dict['鹤壁市'] = '鹤壁'\n",
    "    rename_dict['长垣'] = '长垣县' # + 2020-01-31 2020-02-01\n",
    "    \n",
    "    ###### 甘肃 Gansu\n",
    "    rename_dict['天水市'] = '天水'\n",
    "    rename_dict['平凉市'] = '平凉'\n",
    "    rename_dict['白银市'] = '白银'\n",
    "    rename_dict['金昌市'] = '金昌' # = 2020-01-28\n",
    "    \n",
    "    ###### 广东 Guangdong\n",
    "    rename_dict['河源市'] = '河源' # + 2020-01-28\n",
    "    rename_dict['外地来穗人员'] = '外地来粤人员' # = 2020-01-29\n",
    "    \n",
    "    ###### 海南 Hainan\n",
    "    rename_dict['东方市'] = '东方' # + 2020-01-31\n",
    "    rename_dict['琼海市'] = '琼海' # + 2020-01-31\n",
    "    rename_dict['临高县'] = '临高' # + 2020-01-31\n",
    "    rename_dict['澄迈县'] = '澄迈'\n",
    "    rename_dict['琼中县'] = '琼中'\n",
    "    rename_dict['陵水县'] = '陵水'\n",
    "        \n",
    "    ###### 河北 Hebei\n",
    "    rename_dict['邯郸市'] = '邯郸' # + 2020-01-27 2020-01-28\n",
    "    \n",
    "    ###### 湖南 Hunan\n",
    "    rename_dict['湘西自治州'] = '湘西州' # rename\n",
    "    \n",
    "    ###### 吉林 Jilin\n",
    "    rename_dict['吉林市'] = '吉林'\n",
    "    rename_dict['四平市'] = '四平' \n",
    "    \n",
    "    ###### 内蒙古 Inner Mongolia\n",
    "    rename_dict['乌海市'] = '乌海'\n",
    "    rename_dict['锡林郭勒'] = '锡林郭勒盟' # simply rename it\n",
    "    rename_dict['包头市东河区'] = '包头' # + 2020-01-27 ？######\n",
    "    rename_dict['包头市昆都仑区'] = '包头' # + 2020-01-27 ？######\n",
    "    rename_dict['赤峰市松山区'] = '赤峰' # * 2020-01-27\n",
    "    rename_dict['赤峰市林西县'] = '赤峰' # * 2020-01-27\n",
    "    rename_dict['通辽市经济开发区'] = '通辽'\n",
    "    rename_dict['鄂尔多斯东胜区'] = '鄂尔多斯' # * 2020-01-27\n",
    "    rename_dict['鄂尔多斯鄂托克前旗'] = '鄂尔多斯' # * 2020-01-27\n",
    "    rename_dict['兴安盟乌兰浩特'] = '兴安盟' # + 2020-01-27\n",
    "    rename_dict['呼伦贝尔满洲里'] = '呼伦贝尔' # * 2020-01-27\n",
    "    rename_dict['满洲里'] = '呼伦贝尔' # \n",
    "    rename_dict['呼伦贝尔牙克石'] = '呼伦贝尔'\n",
    "    rename_dict['呼伦贝尔牙克石市'] = '呼伦贝尔' # 满洲里 + 牙克石 = 呼伦贝尔\n",
    "    rename_dict['锡林郭勒盟二连浩特'] = '锡林郭勒盟' # *\n",
    "    rename_dict['锡林郭勒盟锡林浩特'] = '锡林郭勒盟' # *\n",
    "    \n",
    "    ###### 宁夏 Ningxia\n",
    "    rename_dict['西宁市'] = '西宁' # \n",
    "    rename_dict['宁东管委会'] = '宁东' # *\n",
    "    \n",
    "    ###### 青海 Qinghai\n",
    "    rename_dict['北海州'] = '海北州' # *\n",
    "    \n",
    "    ###### 山东 Shandong\n",
    "    rename_dict['淄博市'] = '淄博' # + 2020-01-26\n",
    "    \n",
    "    ###### 山西 Shanxi\n",
    "    rename_dict['临汾市'] = '临汾'\n",
    "    rename_dict['朔州市'] = '朔州' # in\n",
    "    \n",
    "    ###### 上海 Shanghai\n",
    "    rename_dict['杨浦'] = '杨浦区' # + 2020-01-31\n",
    "    rename_dict['松江'] = '松江区' # + 2020-01-31\n",
    "    rename_dict['金山'] = '金山区' # + 2020-01-31\n",
    "    rename_dict['浦东'] = '浦东区'\n",
    "    rename_dict['浦东新区'] = '浦东区'\n",
    "    rename_dict['虹口'] = '虹口区'\n",
    "    rename_dict['黄浦'] = '黄浦区'\n",
    "    rename_dict['宝山'] = '宝山区'\n",
    "    rename_dict['闵行'] = '闵行区'\n",
    "    rename_dict['徐汇'] = '徐汇区'\n",
    "    rename_dict['嘉定'] = '嘉定区'\n",
    "    \n",
    "    ###### 四川 Sichuan\n",
    "    rename_dict['凉山'] = '凉山州' # + and rename\n",
    "    #rename_dict['凉山州'] = '凉山彝族自治州' # + and rename\n",
    "    \n",
    "    ###### 天津 Tianjin\n",
    "    rename_dict['外地来津'] = '外地来津人员' # + 2020-01-29\n",
    "    \n",
    "    ###### 新疆 Xinjiang\n",
    "    rename_dict['吐鲁番市'] = '吐鲁番'   # raw data has both 吐鲁番市 and 吐鲁番, should be combined\n",
    "    rename_dict['昌吉'] = '昌吉州' # + and rename\n",
    "    rename_dict['第八师'] = '石河子'\n",
    "    rename_dict['第八师石河子'] = '石河子'\n",
    "    rename_dict['第八师石河子市'] = '石河子'\n",
    "    rename_dict['兵团第八师石河子市'] = '石河子'\n",
    "    rename_dict['兵团第五师五家渠市'] = '五家渠' # + and rename\n",
    "    rename_dict['第六师'] = '五家渠' # + and rename\n",
    "    rename_dict['兵团第六师五家渠市'] = '五家渠' # + and rename\n",
    "    rename_dict['阿克苏地区'] = '阿克苏' # +\n",
    "    rename_dict['第七师'] = '兵团第七师' # + \n",
    "    rename_dict['第九师'] = '兵团第九师'\n",
    "    \n",
    "    ###### 云南 Yunnan\n",
    "    rename_dict['丽江市'] = '丽江' # + \n",
    "    rename_dict['大理'] = '大理州' # +? and rename \n",
    "    rename_dict['德宏'] = '德宏州' # +? and rename\n",
    "    rename_dict['文山州'] = '文山' # + and rename 2020-02-10\n",
    "    rename_dict['楚雄'] = '楚雄州' # + and rename 2020-02-10\n",
    "    rename_dict['红河'] = '红河州' # + and rename 2020-02-10\n",
    "    rename_dict['西双版纳'] = '西双版纳傣族自治州'\n",
    "    rename_dict['西双版纳州'] = '西双版纳傣族自治州'\n",
    "    \n",
    "    ###### Unknown \n",
    "    ###### 待明确地区 未明确地区 未知 未知地区 待明确 未明确 不明地区\n",
    "    rename_dict['待明确'] = '待明确地区' ######\n",
    "    rename_dict['未明确'] = '待明确地区' \n",
    "    rename_dict['未明确地区'] = '待明确地区'\n",
    "    rename_dict['未知'] = '待明确地区'\n",
    "    rename_dict['未知地区'] = '待明确地区'\n",
    "    rename_dict['不明地区'] = '待明确地区' ######\n",
    "\n",
    "    snapshots['city_name'] = snapshots['city_name'].replace(rename_dict) # rename cities\n",
    "    snapshots.loc[(snapshots['province_name'] == '北京市') & (snapshots['city_name'] == '朝阳'), 'city_name'] = '朝阳区' # only for 朝阳区 in 北京市\n",
    "    return snapshots\n",
    "\n",
    "# Calculate new confirmed, new dead, and new cured\n",
    "def add_daily_new(df, group_keys=['province_name', 'city_name']):\n",
    "    cols = ['confirmed', 'dead', 'cured']\n",
    "    daily_new = df.groupby(group_keys).agg(dict([(n, 'diff') for n in ['cum_' + c for c in cols]]))\n",
    "    daily_new = daily_new.rename(columns=dict([('cum_' + n, 'new_' + n) for n in cols]))\n",
    "    df = pd.concat([df, daily_new], axis=1, join='outer')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data from JHU\n",
    "def load_jhu_raw(verbose = False):\n",
    "    date_list = pd.date_range(_JHS_DATA_START_DATE, datetime.date.today())\n",
    "    frm_list = []\n",
    "    for date in date_list:\n",
    "        if verbose:\n",
    "            print(\"Reading: \" + str(date))\n",
    "        try:\n",
    "            frm = pd.read_csv(_JHS_DATA_PATH_ + date.strftime('%m-%d-%Y') + '.csv')\n",
    "            frm_list.append(frm)\n",
    "        except:\n",
    "            continue\n",
    "            \n",
    "    out = pd.concat(frm_list, sort = False).drop_duplicates()\n",
    "\n",
    "    rename_dict = {'Province/State': 'province/state', \n",
    "                   'Country/Region': 'country/region',\n",
    "                   'Last Update': 'last_update',\n",
    "                   'Confirmed': 'cum_confirmed',\n",
    "                   'Deaths': 'cum_dead',\n",
    "                   'Recovered': 'cum_cured',  \n",
    "                   'Latitude': 'latitude',\n",
    "                   'Longitude': 'longitude',\n",
    "                  }\n",
    "\n",
    "    out = out.rename(columns = rename_dict)\n",
    "\n",
    "    out_others = out[~out['Country_Region'].isnull()]\n",
    "    out_others = pd.concat([out_others.iloc[:,8:-1], out_others.iloc[:,3:6]], axis=1, sort=False)\n",
    "    out = out[out['Country_Region'].isnull()].iloc[:,0:8]\n",
    "\n",
    "    out['update_time'] = pd.to_datetime(out['last_update'])\n",
    "    out['update_date'] = out['update_time'].dt.date\n",
    "    out = out.drop(columns = ['last_update'])\n",
    "    out = out.sort_values(by = ['update_time', 'country/region', 'province/state'])\n",
    "    out = out.reset_index(drop = True)\n",
    "\n",
    "    rename_dict = {'FIPS': 'fips', \n",
    "                   'Admin2': 'county',\n",
    "                   'Province_State': 'province/state',\n",
    "                   'Country_Region': 'country/region',\n",
    "                   'Last_Update': 'last_update',\n",
    "                   'Lat': 'latitude',\n",
    "                   'Long_': 'longitude',\n",
    "                   'Active': 'active'\n",
    "                  }\n",
    "    out_others = out_others.rename(columns = rename_dict)\n",
    "\n",
    "    out_others['update_time'] = pd.to_datetime(out_others['last_update'])\n",
    "    out_others['update_date'] = out_others['update_time'].dt.date\n",
    "    out_others = out_others.drop(columns = ['last_update'])\n",
    "\n",
    "    out_others_us = out_others[out_others['country/region'] =='US']\n",
    "    out_others_us = out_others_us.sort_values(by = ['update_time', 'province/state', 'county'])\n",
    "    out_others_us = out_others_us.reset_index(drop = True)\n",
    "\n",
    "    out_others = out_others[out_others['country/region'] !='US'].iloc[:,2:]\n",
    "    out_others = out_others.sort_values(by = ['update_time', 'country/region', 'province/state'])\n",
    "    out_others = out_others.reset_index(drop = True)\n",
    "\n",
    "    # only keep one piece of data every single day\n",
    "\n",
    "\n",
    "    out.drop_duplicates(subset = ['update_date', 'province/state', 'country/region'], keep = 'last', inplace = True)\n",
    "    out_others_us.drop_duplicates(subset = ['update_date', 'county', 'province/state'], keep = 'last', inplace = True) \n",
    "    out_others.drop_duplicates(subset = ['update_date', 'province/state', 'country/region'], keep = 'last', inplace = True) \n",
    "\n",
    "\n",
    "    out = out[['update_date', 'province/state', 'country/region', 'cum_confirmed', 'cum_dead', 'cum_cured', 'latitude', 'longitude']]\n",
    "    out_others_us = out_others_us[['update_date', 'county', 'province/state', 'cum_confirmed', 'cum_dead', 'cum_cured', 'latitude', 'longitude']]\n",
    "    out_others = out_others[['update_date', 'province/state', 'country/region', 'cum_confirmed', 'cum_dead', 'cum_cured', 'latitude', 'longitude']]\n",
    "\n",
    "    return out, out_others_us, out_others\n",
    "\n",
    "def jhu_daily(jhu_raw):\n",
    "    frm_list = []\n",
    "    for key, frm in jhu_raw.groupby([ 'update_date', 'province/state', 'country/region']):\n",
    "        frm_list.append(frm[-1:])    \n",
    "    out = pd.concat(frm_list).sort_values(['update_date',  'country/region', 'province/state'])\n",
    "    out = add_daily_new(out, group_keys = ['country/region', 'province/state'])\n",
    "    return out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data from DXY\n",
    "def load_chinese_raw():\n",
    "    '''\n",
    "    This provides a way to lookinto the 'raw' data\n",
    "    '''\n",
    "    raw = pd.read_csv(_DXY_DATA_FILE_)\n",
    "    \n",
    "    # the original CSV column names are in camel case, change to lower_case convention\n",
    "    rename_dict = {'updateTime': 'update_time',\n",
    "                   'continentName': 'continent_name',\n",
    "                   'continentEnglishName': 'continent_name_en',\n",
    "                   'countryName': 'country_name',\n",
    "                   'countryEnglishName': 'country_name_en',\n",
    "                   'provinceName': 'province_name',\n",
    "                   'provinceEnglishName': 'province_name_en',\n",
    "                   'province_zipCode': 'province_zip_code',\n",
    "                   'cityName': 'city_name',\n",
    "                   'cityEnglishName': 'city_name_en',\n",
    "                   'city_zipCode': 'city_zip_code',\n",
    "                   'province_confirmedCount': 'province_confirmed',\n",
    "                   'province_suspectedCount': 'province_suspected',\n",
    "                   'province_deadCount': 'province_dead',\n",
    "                   'province_curedCount': 'province_cured',\n",
    "                   'city_confirmedCount': 'city_confirmed',\n",
    "                   'city_suspectedCount': 'city_suspected',\n",
    "                   'city_deadCount': 'city_dead',\n",
    "                   'city_curedCount': 'city_cured'\n",
    "                  }\n",
    "    data = raw.rename(columns=rename_dict)\n",
    "    data['update_time'] = pd.to_datetime(data['update_time'])  # original type of update_time after read_csv is 'str'\n",
    "    data['update_date'] = data['update_time'].dt.date   # add date for daily aggregation, if without to_datetime, it would be a dateInt object, difficult to use\n",
    "    \n",
    "    # display basic info\n",
    "    print('Last update: ', data['update_time'].max())\n",
    "    print('Data date range: ', data['update_date'].min(), 'to', data['update_date'].max())\n",
    "    print('Number of rows in raw data: ', data.shape[0])\n",
    "    return data\n",
    "\n",
    "# Cleaning of data\n",
    "# Not including Taiwan and Hongkong, including Macau and Tibet\n",
    "def load_chinese_data():\n",
    "    ''' This includes some basic cleaning'''\n",
    "    data = load_chinese_raw()\n",
    "    return rename_cities(data)\n",
    "\n",
    "def dxy_daily(df):\n",
    "    \n",
    "    '''Aggregate the frequent time series data into a daily frame, ie, one entry per (date, province, city)'''\n",
    "    \n",
    "    df = df[df['country_name_en'] == 'China']\n",
    "    df = df.drop(df.columns[0:4], axis = 1) # drop continent_name, continent_name_en, country_name, and country_name_en\n",
    "    df = df[~df['province_name_en'].isin(['Hong Kong', 'Macau', 'Taiwan', 'Tibet', 'China'])]\n",
    "    df = df.reset_index(drop = True)\n",
    "\n",
    "    frm_list_city, frm_list_province = [], []\n",
    "    to_names = ['confirmed', 'suspected', 'cured', 'dead']\n",
    "    drop_cols = ['province_name_en', 'city_name_en']\n",
    "    drop_province_cols = ['province_' + field for field in to_names] # put these in another dataframe\n",
    "    province_cols = ['province_name', 'province_zip_code', 'update_time', 'update_date']\n",
    "\n",
    "    # city-level data\n",
    "    for key, frm in df.drop(columns = drop_cols + drop_province_cols).sort_values(['update_date']).groupby(['province_name', 'city_name', 'update_date']):\n",
    "        frm_list_city.append(frm.sort_values(['update_time'])[-1:]) # take the latest row within (city, date)\n",
    "    out_city = pd.concat(frm_list_city).sort_values(['update_date', 'province_name', 'city_name']) # convert a list to a dataframe\n",
    "\n",
    "    out_city = out_city.rename(columns=dict([('city_' + d, 'cum_' + d) for d in to_names])) \n",
    "    out_city = out_city.drop(columns=['cum_suspected'])   # the suspected column from csv is not reliable, may keep this when the upstream problem is solved\n",
    "    out_city = add_daily_new(out_city)  # add daily new cases\n",
    "    out_city = add_en_location(out_city)\n",
    "\n",
    "    # province-level data\n",
    "    for key, frm in df[province_cols + drop_province_cols].sort_values(['update_date']).groupby(['province_name', 'update_date']):\n",
    "        frm_list_province.append(frm.sort_values(['update_time'])[-1:]) # take the latest row within\n",
    "    out_province = pd.concat(frm_list_province).sort_values(['update_date', 'province_name'])\n",
    "\n",
    "    out_province = out_province.rename(columns=dict([('province_' + d, 'cum_' + d) for d in to_names])) \n",
    "    out_province = out_province.drop(columns=['cum_suspected'])\n",
    "\n",
    "    # add missing data\n",
    "    # source: http://www.nhc.gov.cn/xcs/yqtb/list_gzbd_2.shtml\n",
    "    # （） （） （） （） （） （） # () stands for missing # * stands for being different\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 15), \n",
    "                                        'cum_confirmed': 41, 'cum_cured': 7, 'cum_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 16), \n",
    "                                        'cum_confirmed': 41, 'cum_cured': 12, 'cum_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 17), \n",
    "                                        'cum_confirmed': 45, 'cum_cured': 15, 'cum_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 18), \n",
    "                                        'cum_confirmed': 62, 'cum_cured': 19, 'cum_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 19), \n",
    "                                        'cum_confirmed': 62 + 59, 'cum_cured': 19 + 5, 'cum_dead': 2 + 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 20), \n",
    "                                        'cum_confirmed': 198, 'cum_cured': 25, 'cum_dead': 3} , ignore_index=True)\n",
    "    # source: DXY\n",
    "    # （）* （）\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 21), \n",
    "                                        'cum_confirmed': 270, 'cum_cured': 25, 'cum_dead': 6} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'cum_confirmed': 375, 'cum_cured': 28, 'cum_dead': 9} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '湖北省' , 'province_zip_code' : 420000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 444, 'cum_cured': 28, 'cum_dead': 17} , ignore_index=True)\n",
    "    # （） （）\n",
    "    out_province = out_province.append({'province_name' : '广东省' , 'province_zip_code' : 440000, 'update_time': None, 'update_date': datetime.date(2020, 1, 20), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '广东省' , 'province_zip_code' : 440000, 'update_time': None, 'update_date': datetime.date(2020, 1, 21), \n",
    "                                        'cum_confirmed': 17, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # （） （） * *\n",
    "    out_province = out_province.append({'province_name' : '北京市' , 'province_zip_code' : 110000, 'update_time': None, 'update_date': datetime.date(2020, 1, 20), \n",
    "                                        'cum_confirmed': 5, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '北京市' , 'province_zip_code' : 110000, 'update_time': None, 'update_date': datetime.date(2020, 1, 21), \n",
    "                                        'cum_confirmed': 10, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '北京市' , 'province_zip_code' : 110000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'cum_confirmed': 14, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '北京市' , 'province_zip_code' : 110000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 26, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '四川省' , 'province_zip_code' : 510000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 7, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # * *\n",
    "    out_province = out_province.append({'province_name' : '浙江省' , 'province_zip_code' : 330000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'cum_confirmed': 5, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '浙江省' , 'province_zip_code' : 330000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 10, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '重庆市' , 'province_zip_code' : 500000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'cum_confirmed': 5, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '江苏省' , 'province_zip_code' : 320000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # （）\n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 21), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '广西壮族自治区' , 'province_zip_code' : 450000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 2, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 2, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # * *\n",
    "    out_province = out_province.append({'province_name' : '河南省' , 'province_zip_code' : 410000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '河南省' , 'province_zip_code' : 410000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 4, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # ()\n",
    "    out_province = out_province.append({'province_name' : '河北省' , 'province_zip_code' : 130000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # ()\n",
    "    out_province = out_province.append({'province_name' : '山西省' , 'province_zip_code' : 140000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 1, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # () ()\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 1, 21), \n",
    "                                        'cum_confirmed': 2, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 5, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "    # *\n",
    "    out_province = out_province.append({'province_name' : '海南省' , 'province_zip_code' : 460000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'cum_confirmed': 4, 'cum_cured': 0, 'cum_dead': 0} , ignore_index=True)\n",
    "\n",
    "    # *'s causes duplications\n",
    "    out_province = out_province.drop_duplicates(subset = ['update_date', 'province_name']) \n",
    "    out_province = out_province.sort_values(['update_date', 'province_name'])\n",
    "    out_province = out_province.reset_index(drop = True)\n",
    "\n",
    "    ######################################## add missing data automatically (including provinces stopped being updated)\n",
    "\n",
    "    names_province = sorted(set(out_province['province_name']))\n",
    "\n",
    "    for name in names_province:\n",
    "        df_single = out_province[out_province.province_name == name].copy()\n",
    "        timespan = (max(df_single.update_date) - min(df_single.update_date)).days + 1\n",
    "        date_last_local = max(df_single.update_date)\n",
    "        date_last = max(out_province.update_date)\n",
    "        if timespan != df_single.shape[0]: # missing data\n",
    "            df_single['update_date'] =  pd.to_datetime(df_single['update_date'])\n",
    "            df_single.set_index('update_date', inplace = True)\n",
    "            df_single = df_single.resample('D').ffill().reset_index()\n",
    "            df_single = df_single[out_province.columns]\n",
    "            df_single['update_date'] = df_single['update_date'].dt.date\n",
    "            out_province = out_province.append(df_single)\n",
    "        if date_last_local < date_last: # if the province is stopped being updated\n",
    "            df_single = df_single.append(df_single.iloc[-1])\n",
    "            df_single.iloc[-1, df_single.columns.get_loc('update_date')] = date_last\n",
    "            df_single['update_date'] =  pd.to_datetime(df_single['update_date'])\n",
    "            df_single.set_index('update_date', inplace = True)\n",
    "            df_single = df_single.resample('D').ffill().reset_index()\n",
    "            df_single = df_single[out_province.columns]\n",
    "            df_single['update_date'] = df_single['update_date'].dt.date\n",
    "            out_province = out_province.append(df_single)\n",
    "\n",
    "    out_province = out_province.drop_duplicates(subset = ['update_date', 'province_name']) \n",
    "    out_province = out_province.sort_values(['update_date', 'province_name'])\n",
    "    out_province = out_province.reset_index(drop = True)\n",
    "    ######################################## add missing data automatically\n",
    "\n",
    "    out_province = add_daily_new(out_province, group_keys=['province_name'])  # add daily new cases\n",
    "    out_province = add_en_location(out_province, tag = 'province')\n",
    "\n",
    "    # rearrange columns\n",
    "    new_col_order_city = ['update_date', 'province_name', 'province_name_en', 'province_zip_code', 'city_name', 'city_name_en', 'city_zip_code', \n",
    "                          'cum_confirmed', 'cum_cured', 'cum_dead', 'new_confirmed', 'new_cured', 'new_dead', 'update_time']\n",
    "    if len(new_col_order_city) != len(out_city.columns):\n",
    "        raise ValueError(\"Some columns are dropped: \", set(out_city.columns).difference(new_col_order_city))\n",
    "    out_city = out_city[new_col_order_city]\n",
    "\n",
    "    new_col_order_province = ['update_date', 'province_name', 'province_name_en', 'province_zip_code', \n",
    "                          'cum_confirmed', 'cum_cured', 'cum_dead', 'new_confirmed', 'new_cured', 'new_dead', 'update_time']\n",
    "    if len(new_col_order_province) != len(out_province.columns):\n",
    "        raise ValueError(\"Some columns are dropped: \", set(out_province.columns).difference(new_col_order_province))\n",
    "    out_province = out_province[new_col_order_province]\n",
    "\n",
    "    # convert from int to float\n",
    "    out_city[['cum_confirmed', 'cum_cured', 'cum_dead']] = out_city[['cum_confirmed', 'cum_cured', 'cum_dead']].astype(float)\n",
    "    out_province[['cum_confirmed', 'cum_cured', 'cum_dead']] = out_province[['cum_confirmed', 'cum_cured', 'cum_dead']].astype(float)\n",
    "\n",
    "    # sort and reindex\n",
    "    out_city = out_city.sort_values(['update_date', 'province_name_en', 'city_name_en'])\n",
    "    out_city = out_city.reset_index(drop = True)\n",
    "\n",
    "    out_province = out_province.sort_values(['update_date', 'province_name_en'])\n",
    "    out_province = out_province.reset_index(drop = True)\n",
    "    \n",
    "    return out_city, out_province   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data from Kaggle (out-dated)\n",
    "def load_kaggle_raw():\n",
    "    raw = pd.read_csv(_Kaggle_DATA_PATH_)\n",
    "    out_country = raw[(raw.country == '中国') & (raw.province.isnull()) & (raw.city.isnull())]\n",
    "    \n",
    "    out_city = raw[(raw.country == '中国') & (raw.province.notnull()) & (raw.city.notnull())]\n",
    "    out_city = out_city.drop(['country', 'countryCode'], axis=1)\n",
    "    rename_dict = {'date': 'update_date',\n",
    "                   'province': 'province_name',\n",
    "                   'provinceCode': 'province_zip_code',\n",
    "                   'city': 'city_name',\n",
    "                   'cityCode': 'city_zip_code',\n",
    "                   'confirmed': 'cum_confirmed',\n",
    "                   'suspected': 'cum_suspected',\n",
    "                   'cured': 'cum_cured',\n",
    "                   'dead': 'cum_dead'\n",
    "                      }\n",
    "    out_city = out_city.rename(columns = rename_dict)\n",
    "    out_city = add_en_location(out_city)\n",
    "    out_city['update_date'] = pd.to_datetime(out_city['update_date'])  \n",
    "    out_city['update_date'] = out_city['update_date'].dt.date\n",
    "    out_city = out_city.sort_values(by = ['update_date', 'province_name', 'city_name'])\n",
    "    out_city = out_city.reset_index(drop = True)\n",
    "    new_cols = ['update_date', 'province_name', 'province_name_en', 'province_zip_code', \n",
    "                'city_name', 'city_name_en', 'city_zip_code',\n",
    "                'cum_confirmed', 'cum_suspected', 'cum_cured', 'cum_dead']\n",
    "    out_city = out_city[new_cols]\n",
    "    \n",
    "    out_province = raw[(raw.country == '中国') & (raw.province.notnull()) & (raw.city.isnull())]\n",
    "    out_province = out_province.drop(['country', 'countryCode', 'city', 'cityCode'], axis=1)\n",
    "    rename_dict = {'date': 'update_date',\n",
    "                   'province': 'province_name',\n",
    "                   'provinceCode': 'province_zip_code',\n",
    "                   'confirmed': 'cum_confirmed',\n",
    "                   'suspected': 'cum_suspected',\n",
    "                   'cured': 'cum_cured',\n",
    "                   'dead': 'cum_dead'\n",
    "                      }\n",
    "    out_province = out_province.rename(columns = rename_dict)\n",
    "    out_province = add_en_location(out_province, tag = 'province')\n",
    "    out_province['update_date'] = pd.to_datetime(out_province['update_date'])  \n",
    "    out_province['update_date'] = out_province['update_date'].dt.date   \n",
    "    out_province = out_province[~out_province.province_name.isin(['香港特别行政区','澳门特别行政区','台湾省'])]\n",
    "    \n",
    "    out_province = out_province.sort_values(by = ['update_date', 'province_name'])\n",
    "    out_province = out_province.reset_index(drop = True)\n",
    "    \n",
    "    new_cols = ['update_date', 'province_name', 'province_name_en', 'province_zip_code', \n",
    "               'cum_confirmed', 'cum_suspected', 'cum_cured', 'cum_dead']\n",
    "    out_province = out_province[new_cols]\n",
    "    \n",
    "    return out_city, out_province"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data from JHU is incomplete !!!\n",
    "data, data_others_us, data_others = load_jhu_raw()\n",
    "data_JHU = jhu_daily(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last update:  2020-04-28 23:34:00\n",
      "Data date range:  2020-01-22 to 2020-04-28\n",
      "Number of rows in raw data:  155697\n"
     ]
    }
   ],
   "source": [
    "data = load_chinese_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_city, data_province = dxy_daily(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>update_date</th>\n",
       "      <th>province_name</th>\n",
       "      <th>province_name_en</th>\n",
       "      <th>province_zip_code</th>\n",
       "      <th>cum_confirmed</th>\n",
       "      <th>cum_cured</th>\n",
       "      <th>cum_dead</th>\n",
       "      <th>new_confirmed</th>\n",
       "      <th>new_cured</th>\n",
       "      <th>new_dead</th>\n",
       "      <th>update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [update_date, province_name, province_name_en, province_zip_code, cum_confirmed, cum_cured, cum_dead, new_confirmed, new_cured, new_dead, update_time]\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Double check if there is no missing rows\n",
    "def missing_data():\n",
    "    for name in names_province:\n",
    "        df_single = data_province[data_province.province_name_en == name]\n",
    "        timespan = (max(df_single.update_date) - min(df_single.update_date)).days + 1\n",
    "        if (timespan != df_single.shape[0]) or (max(df_single.update_date)!= max(data_province.update_date)):\n",
    "            print(name)\n",
    "missing_data()\n",
    "# Double check that there is no duplicated rows\n",
    "data_province[data_province.duplicated(['update_date', 'province_name'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare different data sources\n",
    "#data_JHU[data_JHU['province/state'] == 'Hubei']\n",
    "#data_province[data_province['province_name_en'] == 'Hubei']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the data of Mainland China (national)\n",
    "export_JHU = data_JHU[data_JHU['country/region'] == 'Mainland China'].groupby('update_date').agg('sum').drop(columns=['latitude','longitude'])\n",
    "export_JHU.to_csv(r'./data/data_JHU.csv', index = False)\n",
    "export_DXY_city = data_city.groupby('update_date').agg('sum').drop(columns=['province_zip_code', 'city_zip_code'])\n",
    "export_DXY_city.to_csv(r'./data/data_DXY_city.csv', index = False)\n",
    "export_DXY_province = data_province.groupby('update_date').agg('sum').drop(columns=['province_zip_code'])\n",
    "export_DXY_province.to_csv(r'./data/data_DXY_province.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_city, data_province, data_province_domestic = load_DXY_raw()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add new cases of the first day for every province\n",
    "new_columns = ['new_confirmed', 'new_cured', 'new_dead']\n",
    "columns = ['cum_confirmed', 'cum_cured', 'cum_dead']\n",
    "for i, col in enumerate(new_columns):\n",
    "    data_city[col].fillna(data_city[columns[i]], inplace = True)\n",
    "    data_province[col].fillna(data_province[columns[i]], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Beijing', 'Gansu', 'Shandong', 'Shanghai', 'Zhejiang']\n"
     ]
    }
   ],
   "source": [
    "# Find those provinces with imported cases\n",
    "data_city_imported = data_city[data_city.city_name.str.contains('境外')]\n",
    "data_city_imported = data_city_imported.fillna(0)\n",
    "# Get the list of provinces with imported cases\n",
    "names_province_imported = sorted(set(data_city_imported['province_name_en']))\n",
    "print(names_province_imported)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove import cases and get the data set consisting of only domestic cases\n",
    "def dxy_domestic(data, data_city_imported):\n",
    "    \n",
    "    def no_imported_cases(row, data_city_imported, col):\n",
    "        if row.province_name_en not in names_province_imported:\n",
    "            return row[col]\n",
    "        else:\n",
    "            data_imported = data_city_imported[data_city_imported.province_name_en == row.province_name_en]\n",
    "            date_imported_first = data_imported.update_date.tolist()[0]\n",
    "            date_imported_last = data_imported.update_date.tolist()[-1]\n",
    "            if row.update_date < date_imported_first: # before the first imported case appeared\n",
    "                return row[col]\n",
    "            elif row.update_date < date_imported_last: \n",
    "                temp = row[col] - data_imported[data_imported.update_date == row.update_date][col] # remove those imported cases\n",
    "                #print (temp, row.province_name_en, row[col], data_imported[data_imported.update_date == row.update_date][col])\n",
    "                return int(temp)\n",
    "            else:\n",
    "                temp = row[col] - data_imported[data_imported.update_date == date_imported_last][col]\n",
    "                return int(temp)\n",
    "    \n",
    "    df = data.copy()\n",
    "    columns = ['cum_confirmed', 'cum_cured', 'cum_dead', 'new_confirmed', 'new_cured', 'new_dead']\n",
    "    \n",
    "    for col in columns:\n",
    "        df[col] = data.apply(lambda row: no_imported_cases(row, data_city_imported, col), axis=1)\n",
    "        # replace negative numbers by zero \n",
    "        # why there are negative numbers: how DXY update the data (some city level data is missing)    \n",
    "        df[col] = df[col].mask(df[col].lt(0), 0)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_province_domestic = dxy_domestic(data_province, data_city_imported)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the data of Mainland China \n",
    "# city level\n",
    "# province level\n",
    "\n",
    "#data_city.to_csv(r'./data/data_DXY_city_all.csv', index = False)\n",
    "#data_province.to_csv(r'./data/data_DXY_province_all.csv', index = False)\n",
    "#data_province_domestic.to_csv(r'./data/data_DXY_province_all_domestic.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_k_city, data_k_province = load_kaggle_raw()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Redundant names of cities\n",
    "\n",
    "### 市 盟 州 县\n",
    "#### 邯郸（市）乌海（市）河源（市）临汾（市）朔州（市）天水（市）平凉（市）\n",
    "#### 白银（市）金昌（市）凉山州 东方（市）临高（县）澄迈（县）琼中（县）\n",
    "#### 琼海（市） 陵水（县）\n",
    "#### 杨浦区 松江区 金山区 浦东（新）区 \n",
    "#### 丽江（市） 大理州 德宏州 文山（州）楚雄州 红河州 西双版纳州\n",
    "#### 大兴区 怀柔区 昌平区 朝阳区 海淀区 石景山区 西城区 顺义区 东城区 丰台区 通州区 门头沟区\n",
    "#### 西宁（市） 吉林（市） 四平（市）\n",
    "#### 云阳县 巫山县 巫溪县 丰都县 垫江县 城口县 奉节县 石柱县 秀山县 酉阳县\n",
    "#### 湘西（自治）州\n",
    "#### 南阳（含邓州）商丘（含永城）安阳（市）  安阳（含滑县）新乡（含长垣）长垣县 漯河（市） 鹤壁（市）\n",
    "#### 吐鲁番（市）昌吉州 第八师 = 第八师石河子 = 第八师石河子市 = 兵团第八师石河子市 = 石河子\n",
    "#### 兵团第五师五家渠市 = 兵团第六师五家渠市 = 五家渠\n",
    "#### 阿克苏（地区）\n",
    "#### 淄博（市）\n",
    "#### 锡林郭勒盟\n",
    "\n",
    "### 内蒙古\n",
    "#### 包头市（东河区）包头市（昆都仑区）呼和浩特（新城区）\n",
    "#### 赤峰市（松山区）赤峰市（林西县）\n",
    "#### 通辽市（经济开发区）\n",
    "#### 鄂尔多斯（东胜区）鄂尔多斯（鄂托克前旗）\n",
    "#### 兴安盟（乌兰浩特）\n",
    "#### 呼伦贝尔（满洲里）呼伦贝尔（牙克石）呼伦贝尔（牙克石市）\n",
    "#### 锡林郭勒盟（二连浩特）锡林郭勒盟（锡林浩特）\n",
    "\n",
    "### 管委会\n",
    "#### 宁东（管委会）\n",
    "\n",
    "### 外地\n",
    "#### 外地来穗人员 外地来粤人员 外地来京人员 武汉来京人员 外地来沪人员 外地来津人员（外地来津） 境外输入人员 \n",
    "\n",
    "### 待明确地区\n",
    "#### 待明确地区 未明确地区 未知 未知地区 待明确 未明确 不明地区"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### 监狱\n",
    "# 湖北 监狱系统\n",
    "# 山东 任城监狱\n",
    "# 浙江 省十里丰监狱\n",
    "### \n",
    "# 沧州 is in 河北，instead of 河南"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Baidu Qianxi compared with DXY with respect to the naming of cities\n",
    "\n",
    "### 安徽\n",
    "#### 合肥 安庆 蚌埠 亳州 池州 滁州 阜阳 淮北 黄山 六安 马鞍山 宿州 铜陵 芜湖 宣城 淮南\n",
    "##### 宿松（直管县）歙县（隶属黄山）\n",
    "\n",
    "### 澳门\n",
    "##### 澳门\n",
    "\n",
    "### 北京\n",
    "#### \n",
    "\n",
    "### 重庆\n",
    "\n",
    "### 福建\n",
    "#### 福州 龙岩 南平 宁德 莆田 泉州 三明 厦门 漳州\n",
    "\n",
    "### 海南\n",
    "#### 海口 白沙黎族自治县 保亭（黎族苗族自治县） 昌江（黎族自治县）\n",
    "#### 儋州 澄迈 东方 定安 琼海 琼中（黎族苗族自治县） 乐东（黎族自治县）\n",
    "#### 临高 陵水（黎族自治县） 三亚 屯昌 万宁 文昌 五指山 三沙\n",
    "\n",
    "### 河北\n",
    "#### 石家庄 保定 沧州 承德 邯郸 衡水 廊坊 秦皇岛 唐山 邢台 张家口\n",
    "\n",
    "### 河南\n",
    "#### 郑州 安阳 鹤壁 焦作 开封 洛阳 漯河 南阳 平顶山 濮阳 三门峡 商丘 新乡 信阳 许昌 周口 驻马店 济源\n",
    "##### 固始县（直管县，隶属信阳）巩义（直管县，隶属郑州/洛阳）永城（商丘市代管县级市），\n",
    "##### 滑县（直管县）邓州（直管县，隶属安阳）长垣县（直管县，隶属新乡）\n",
    "\n",
    "### 黑龙江\n",
    "#### 哈尔滨 大庆 大兴安岭（地区） 鹤岗 黑河 鸡西 佳木斯 牡丹江 七台河 \n",
    "#### 齐齐哈尔 双鸭山 绥化 伊春\n",
    "\n",
    "### 湖北\n",
    "#### 武汉 鄂州 恩施（恩施州） 黄冈 黄石 荆门 荆州 潜江 神农架林区 十堰 随州 天门 仙桃 咸宁 襄阳 孝感 宜昌\n",
    "#### 恩施土家族苗族自治州\n",
    "\n",
    "### 湖南\n",
    "#### 长沙 常德 郴州 衡阳 怀化 娄底 邵阳 湘潭 湘西州 益阳 永州 岳阳 张家界 株洲\n",
    "#### 湘西土家族苗族自治州\n",
    " \n",
    "### 甘肃\n",
    "#### 兰州 白银 定西 甘南（州） 嘉峪关 金昌 酒泉 临夏（州） 陇南 平凉 庆阳 天水 武威 张掖\n",
    "#### 临夏回族自治州 甘南藏族自治区\n",
    "\n",
    "### 广东\n",
    "#### 广州 潮州 东莞 佛山 河源 惠州 江门 揭阳 茂名 梅州 清远 汕头 汕尾 韶关 深圳 阳江 云浮 湛江 肇庆 中山 珠海\n",
    "\n",
    "### 广西（壮族自治区）\n",
    "#### 南宁 百色 北海 崇左 防城港 桂林 贵港 河池 贺州 来宾 柳州 钦州 梧州 玉林\n",
    "\n",
    "### 贵州\n",
    "#### 贵阳 安顺 毕节（地区） 六盘水 铜仁（地区） 遵义 黔西南州 黔东南州 黔南州\n",
    "#### 黔东南苗族侗族自治州 黔南布依族苗族自治州 黔西南布依族苗族自治州\n",
    "\n",
    "### 吉林\n",
    "#### 长春 白城 白山 吉林市 辽源 四平 松原 通化 延边\n",
    "#### 延边朝鲜族自治州\n",
    "##### 公主岭（隶属四平）梅河口（隶属通化）\n",
    "\n",
    "### 江苏\n",
    "#### 南京 常州 淮安 连云港 南通 苏州 宿迁 泰州 无锡 徐州 盐城 扬州 镇江\n",
    "\n",
    "### 江西\n",
    "#### 南昌 抚州 赣州 吉安 景德镇 九江 萍乡 上饶 新余 宜春 鹰潭\n",
    "##### 赣江新区（国家级新区，隶属南昌）\n",
    "\n",
    "### 辽宁\n",
    "#### 沈阳 鞍山 本溪 朝阳 大连 丹东 抚顺 阜新 葫芦岛 锦州 辽阳 盘锦 铁岭 营口\n",
    "\n",
    "### 内蒙古（自治区）\n",
    "#### 呼和浩特 阿拉善盟 包头 巴彦淖尔 赤峰 鄂尔多斯 呼伦贝尔 通辽 乌海 乌兰察布 锡林郭勒盟 兴安盟\n",
    "#### 满洲里（隶属呼伦贝尔）\n",
    "\n",
    "### 宁夏（回族自治区）\n",
    "#### 银川 固原 石嘴山 吴忠 中卫\n",
    "#### 宁东\n",
    "\n",
    "### 青海\n",
    "#### 西宁 果洛州 海东地区 海北州 海南州 海西州 黄南州 玉树州\n",
    "#### 海北藏族自治州\n",
    "#### 北海州（typo）\n",
    "\n",
    "### 上海\n",
    "#### \n",
    "\n",
    "### 陕西：\n",
    "#### 西安 安康 宝鸡 汉中 商洛 铜川 渭南 咸阳 延安 榆林 \n",
    "##### 杨凌（区，隶属咸阳）， 韩城（隶属渭南）\n",
    "\n",
    "### 山东\n",
    "#### 济南 滨州 东营 德州 菏泽 济宁 聊城 临沂 青岛 日照 泰安 威海 潍坊 烟台 枣庄 淄博\n",
    "\n",
    "### 山西:\t\n",
    "#### 太原 长治 大同 晋城 晋中 临汾 吕梁 朔州 忻州 阳泉 运城\n",
    "\n",
    "### 四川：\n",
    "#### 成都 阿坝州 巴中 达州 德阳 甘孜州 广安 广元 乐山 凉山州\n",
    "#### 泸州 南充 眉山 绵阳 内江 攀枝花 遂宁 雅安 宜宾 资阳 自贡\n",
    "#### 阿坝藏族羌族自治州 凉山彝族自治州 甘孜藏族自治州\n",
    "\n",
    "### 天津：\n",
    "####\n",
    "\n",
    "### 西藏：\n",
    "#### 拉萨 阿里地区 昌都地区 林芝地区 那曲地区 日喀则地区 山南地区\n",
    "\n",
    "### 新疆：\n",
    "#### 乌鲁木齐 阿拉尔 阿克苏（地区） 阿勒泰地区 巴（音郭楞蒙古自治）州 博尔塔拉州 昌吉州\n",
    "#### 哈密地区 和田地区 喀什地区 克拉玛依 克孜勒苏州 石河子 塔城（地区） 图木舒克 吐鲁番（地区） \n",
    "#### 五家渠 伊犁州 北屯 铁门关 双河 可克达拉 昆玉\n",
    "#### 昌吉回族自治州\n",
    "##### 兵团第四师 兵团第七师 兵团第九师 兵团第十二师 第六师 第七师 第九师 胡杨河\n",
    "\n",
    "#### 云南\n",
    "#### 昆明 保山 楚雄州 大理州 德宏州 迪庆州 红河州 丽江 临沧 怒江州 \n",
    "#### 普洱 曲靖 昭通 文山 西双版纳傣族自治州 玉溪\n",
    "#### 大理白族自治州 德宏傣族景颇族自治州 文山壮族苗族自治州 楚雄彝族自治州\n",
    "#### 红河哈尼族彝族自治州 西双版纳傣族自治州\n",
    "\n",
    "### 浙江：\n",
    "#### 杭州 湖州 嘉兴 金华 丽水 宁波 衢州 绍兴 台州 温州 舟山"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Missing data added for provinces (cum_confirmed, cum_dead, cum_cured)\n",
    "#### Hubei 01-21 to 01-23: \n",
    "# 270, 6, 25\n",
    "# 375, 9, 28\n",
    "# 444, 17, 28\n",
    "#### Shandong (JHU) 01-22 to 01-23:\n",
    "# 2, 0, 0\n",
    "# 6, 0, 0\n",
    "#### Guangdong 01-20 to 01-23:\n",
    "# 1, 0, 0\n",
    "# 17, 0, 0\n",
    "# 26, 0, 0\n",
    "# 32, 0, 0\n",
    "#### Beijing 01-20 to 01-23\n",
    "# 5, 0, 0\n",
    "# 10, 0, 0\n",
    "# 14, 0, 0\n",
    "# 26, 0, 0\n",
    "#### Sichuan 01-22 to 01-23\n",
    "# 5, 0, 0 (JHU)\n",
    "# 7, 0, 0\n",
    "#### Heilongjiang\n",
    "#### Zhejiang 01-22 to 01-23\n",
    "# 5, 0, 0\n",
    "# 10, 0, 0\n",
    "#### Hunan\n",
    "#### Chongqing 01-22 to 01-23\n",
    "# 5, 0, 0\n",
    "# 9, 0, 0\n",
    "#### Hongkong 01-23\n",
    "# 1, 0, 0\n",
    "#### Jiangsu 01-23\n",
    "# 1, 0, 0\n",
    "#### Shanghai 01-21 to 01-26\n",
    "# 1, 0, 0\n",
    "# 9, 0, 0\n",
    "# 16, 0, 0\n",
    "# 20, 0, 0\n",
    "# 33, 1, 0\n",
    "# 40, 1, 1\n",
    "#### Guangxi 01-23\n",
    "# 2, 0, 0\n",
    "#### Guizhou\n",
    "#### Jiangxi 01-22 to 01-23\n",
    "# 2, 0, 0\n",
    "# 3, 0, 0\n",
    "#### Shaanxi\n",
    "#### Liaoning 01-23\n",
    "# 2, 0, 0\n",
    "#### Fujian (JHU) 01-22 to 01-23\n",
    "# 1, 0, 0\n",
    "# 5, 0, 0\n",
    "#### Anhui 01-23\n",
    "# 9, 0, 0\n",
    "#### Gansu\n",
    "#### Henan 01-22 to 01-23\n",
    "# 1, 0, 0\n",
    "# 4, 0, 0\n",
    "#### Inner Mongolia (error)\n",
    "#### Hebei 01-23\n",
    "# 1, 0, 0\n",
    "#### Shanxi 01-23\n",
    "# 1, 0, 0\n",
    "#### Ningxia\n",
    "#### Tianjin 01-21 to 01-25\n",
    "# 2, 0, 0\n",
    "# 4, 0, 0\n",
    "# 5, 0, 0\n",
    "# 8, 0, 0\n",
    "# 10, 0, 0\n",
    "#### Hainan 01-23\n",
    "# 4, 0, 0\n",
    "#### Jilin\n",
    "#### Xinjiang 01-24\n",
    "# 2, 0, 0\n",
    "#### Macau\n",
    "#### Qinghai\n",
    "#### Tibet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### missing data\n",
    "#### data from DXY only got updated if there was new changes\n",
    "\n",
    "#### extra column: 'province_suspected'\n",
    "\n",
    "# front\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '山东省' , 'province_zip_code' : 370000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 2, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '山东省' , 'province_zip_code' : 370000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 6, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '广东省' , 'province_zip_code' : 440000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 26, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '广东省' , 'province_zip_code' : 440000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 32, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '四川省' , 'province_zip_code' : 510000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 5, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '重庆市' , 'province_zip_code' : 500000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 9, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 9, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 16, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 24), \n",
    "                                        'province_confirmed': 20, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 25), \n",
    "                                        'province_confirmed': 33, 'province_suspected': None, 'province_cured': 0, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '上海市' , 'province_zip_code' : 310000, 'update_time': None, 'update_date': datetime.date(2020, 1, 26), \n",
    "                                        'province_confirmed': 40, 'province_suspected': None, 'province_cured': 1, 'province_dead': 1} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '江西省' , 'province_zip_code' : 360000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 2, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '江西省' , 'province_zip_code' : 360000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 3, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '福建省' , 'province_zip_code' : 350000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 1, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '福建省' , 'province_zip_code' : 350000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 5, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '安徽省' , 'province_zip_code' : 340000, 'update_time': None, 'update_date': datetime.date(2020, 1, 23), \n",
    "                                        'province_confirmed': 9, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 1, 22), \n",
    "                                        'province_confirmed': 4, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 1, 24), \n",
    "                                        'province_confirmed': 8, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 1, 25), \n",
    "                                        'province_confirmed': 10, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '新疆维吾尔自治区' , 'province_zip_code' : 650000, 'update_time': None, 'update_date': datetime.date(2020, 1, 24), \n",
    "                                        'province_confirmed': 2, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "# Back\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '安徽省' , 'province_zip_code' : 340000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                        'province_confirmed': 990, 'province_suspected': None, 'province_cured': 981, 'province_dead': 6} , ignore_index=True)\n",
    "    \n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '甘肃省' , 'province_zip_code' : 620000, 'update_time': None, 'update_date': datetime.date(2020, 2, 14), \n",
    "                                            'province_confirmed': 90, 'province_suspected': None, 'province_cured': 44, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '甘肃省' , 'province_zip_code' : 620000, 'update_time': None, 'update_date': datetime.date(2020, 2, 22), \n",
    "                                            'province_confirmed': 91, 'province_suspected': None, 'province_cured': 76, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '甘肃省' , 'province_zip_code' : 620000, 'update_time': None, 'update_date': datetime.date(2020, 2, 25), \n",
    "                                            'province_confirmed': 91, 'province_suspected': None, 'province_cured': 80, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '甘肃省' , 'province_zip_code' : 620000, 'update_time': None, 'update_date': datetime.date(2020, 2, 27), \n",
    "                                            'province_confirmed': 91, 'province_suspected': None, 'province_cured': 82, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '甘肃省' , 'province_zip_code' : 620000, 'update_time': None, 'update_date': datetime.date(2020, 2, 29), \n",
    "                                            'province_confirmed': 91, 'province_suspected': None, 'province_cured': 82, 'province_dead': 2} , ignore_index=True)\n",
    "\n",
    "    out_province.loc[(out_province.update_date == datetime.date(2020,1,31)) & (out_province.province_name == '贵州省'), 'province_confirmed'] = 15\n",
    "    out_province.loc[(out_province.update_date == datetime.date(2020,1,31)) & (out_province.province_name == '贵州省'), 'province_cured'] = 1\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 2, 1), \n",
    "                                            'province_confirmed': 29, 'province_suspected': None, 'province_cured': 2, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 2, 24), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 103, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 2, 26), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 112, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 2, 28), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 112, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 2, 29), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 112, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 3, 1), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 114, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 3, 3), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 114, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 3, 4), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 114, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 3, 5), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 114, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '贵州省' , 'province_zip_code' : 520000, 'update_time': None, 'update_date': datetime.date(2020, 3, 6), \n",
    "                                            'province_confirmed': 146, 'province_suspected': None, 'province_cured': 114, 'province_dead': 2} , ignore_index=True)\n",
    "\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '海南省' , 'province_zip_code' : 460000, 'update_time': None, 'update_date': datetime.date(2020, 3, 2), \n",
    "                                            'province_confirmed': 168, 'province_suspected': None, 'province_cured': 151, 'province_dead': 5} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '海南省' , 'province_zip_code' : 460000, 'update_time': None, 'update_date': datetime.date(2020, 3, 6), \n",
    "                                            'province_confirmed': 168, 'province_suspected': None, 'province_cured': 158, 'province_dead': 6} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '海南省' , 'province_zip_code' : 460000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                            'province_confirmed': 168, 'province_suspected': None, 'province_cured': 158, 'province_dead': 6} , ignore_index=True)\n",
    "\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '河南省' , 'province_zip_code' : 410000, 'update_time': None, 'update_date': datetime.date(2020, 3, 9), \n",
    "                                        'province_confirmed': 1272, 'province_suspected': None, 'province_cured': 1247, 'province_dead': 22} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '河南省' , 'province_zip_code' : 410000, 'update_time': None, 'update_date': datetime.date(2020, 3, 10), \n",
    "                                        'province_confirmed': 1272, 'province_suspected': None, 'province_cured': 1249, 'province_dead': 22} , ignore_index=True)\n",
    "\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '内蒙古自治区' , 'province_zip_code' : 150000, 'update_time': None, 'update_date': datetime.date(2020, 2, 11), \n",
    "                                            'province_confirmed': 60, 'province_suspected': None, 'province_cured': 5, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '内蒙古自治区' , 'province_zip_code' : 150000, 'update_time': None, 'update_date': datetime.date(2020, 2, 25), \n",
    "                                            'province_confirmed': 75, 'province_suspected': None, 'province_cured': 35, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '内蒙古自治区' , 'province_zip_code' : 150000, 'update_time': None, 'update_date': datetime.date(2020, 3, 9), \n",
    "                                            'province_confirmed': 75, 'province_suspected': None, 'province_cured': 70, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '内蒙古自治区' , 'province_zip_code' : 150000, 'update_time': None, 'update_date': datetime.date(2020, 3, 10), \n",
    "                                            'province_confirmed': 75, 'province_suspected': None, 'province_cured': 70, 'province_dead': 1} , ignore_index=True)\n",
    "\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '吉林省' , 'province_zip_code' : 220000, 'update_time': None, 'update_date': datetime.date(2020, 1, 26), \n",
    "                                            'province_confirmed': 4, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '吉林省' , 'province_zip_code' : 220000, 'update_time': None, 'update_date': datetime.date(2020, 3, 3), \n",
    "                                            'province_confirmed': 93, 'province_suspected': None, 'province_cured': 84, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '吉林省' , 'province_zip_code' : 220000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                            'province_confirmed': 93, 'province_suspected': None, 'province_cured': 90, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '吉林省' , 'province_zip_code' : 220000, 'update_time': None, 'update_date': datetime.date(2020, 3, 8), \n",
    "                                            'province_confirmed': 93, 'province_suspected': None, 'province_cured': 90, 'province_dead': 1} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 2, 28), \n",
    "                                            'province_confirmed': 121, 'province_suspected': None, 'province_cured': 95, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 3, 4), \n",
    "                                            'province_confirmed': 125, 'province_suspected': None, 'province_cured': 106, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 3, 5), \n",
    "                                            'province_confirmed': 125, 'province_suspected': None, 'province_cured': 106, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 3, 6), \n",
    "                                            'province_confirmed': 125, 'province_suspected': None, 'province_cured': 106, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '辽宁省' , 'province_zip_code' : 210000, 'update_time': None, 'update_date': datetime.date(2020, 3, 9), \n",
    "                                            'province_confirmed': 125, 'province_suspected': None, 'province_cured': 110, 'province_dead': 1} , ignore_index=True)\n",
    "\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 2, 5), \n",
    "                                            'province_confirmed': 34, 'province_suspected': None, 'province_cured': 1, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 2, 16), \n",
    "                                            'province_confirmed': 70, 'province_suspected': None, 'province_cured': 33, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 2, 22), \n",
    "                                            'province_confirmed': 71, 'province_suspected': None, 'province_cured': 48, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 2, 28), \n",
    "                                            'province_confirmed': 73, 'province_suspected': None, 'province_cured': 68, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 3, 1), \n",
    "                                            'province_confirmed': 74, 'province_suspected': None, 'province_cured': 69, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 3, 3), \n",
    "                                            'province_confirmed': 75, 'province_suspected': None, 'province_cured': 69, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '宁夏回族自治区' , 'province_zip_code' : 640000, 'update_time': None, 'update_date': datetime.date(2020, 3, 5), \n",
    "                                            'province_confirmed': 75, 'province_suspected': None, 'province_cured': 69, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 1, 26), \n",
    "                                            'province_confirmed': 4, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 1, 28), \n",
    "                                            'province_confirmed': 6, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 1, 29), \n",
    "                                            'province_confirmed': 6, 'province_suspected': None, 'province_cured': 0, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 7), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 3, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 8), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 3, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 9), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 3, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 10), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 3, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 14), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 11, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 16), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 13, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 17), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 13, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 20), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 16, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '青海省' , 'province_zip_code' : 630000, 'update_time': None, 'update_date': datetime.date(2020, 2, 22), \n",
    "                                            'province_confirmed': 18, 'province_suspected': None, 'province_cured': 18, 'province_dead': 0} , ignore_index=True)\n",
    "\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '陕西省' , 'province_zip_code' : 610000, 'update_time': None, 'update_date': datetime.date(2020, 3, 3), \n",
    "                                            'province_confirmed': 245, 'province_suspected': None, 'province_cured': 218, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '陕西省' , 'province_zip_code' : 610000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                            'province_confirmed': 245, 'province_suspected': None, 'province_cured': 226, 'province_dead': 1} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '陕西省' , 'province_zip_code' : 610000, 'update_time': None, 'update_date': datetime.date(2020, 3, 9), \n",
    "                                            'province_confirmed': 245, 'province_suspected': None, 'province_cured': 227, 'province_dead': 1} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '山西省' , 'province_zip_code' : 140000, 'update_time': None, 'update_date': datetime.date(2020, 3, 6), \n",
    "                                            'province_confirmed': 133, 'province_suspected': None, 'province_cured': 126, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '山西省' , 'province_zip_code' : 140000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                            'province_confirmed': 133, 'province_suspected': None, 'province_cured': 126, 'province_dead': 0} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '山西省' , 'province_zip_code' : 140000, 'update_time': None, 'update_date': datetime.date(2020, 3, 8), \n",
    "                                            'province_confirmed': 133, 'province_suspected': None, 'province_cured': 126, 'province_dead': 0} , ignore_index=True)\n",
    "    \n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 3, 2), \n",
    "                                            'province_confirmed': 136, 'province_suspected': None, 'province_cured': 118, 'province_dead': 3} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 3, 4), \n",
    "                                            'province_confirmed': 136, 'province_suspected': None, 'province_cured': 128, 'province_dead': 3} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 3, 6), \n",
    "                                            'province_confirmed': 136, 'province_suspected': None, 'province_cured': 128, 'province_dead': 3} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 3, 7), \n",
    "                                            'province_confirmed': 136, 'province_suspected': None, 'province_cured': 128, 'province_dead': 3} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '天津市' , 'province_zip_code' : 120000, 'update_time': None, 'update_date': datetime.date(2020, 3, 8), \n",
    "                                            'province_confirmed': 136, 'province_suspected': None, 'province_cured': 129, 'province_dead': 3} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '新疆维吾尔自治区' , 'province_zip_code' : 650000, 'update_time': None, 'update_date': datetime.date(2020, 2, 25), \n",
    "                                            'province_confirmed': 76, 'province_suspected': None, 'province_cured': 30, 'province_dead': 2} , ignore_index=True)\n",
    "\n",
    "    out_province = out_province.append({'province_name' : '云南省' , 'province_zip_code' : 530000, 'update_time': None, 'update_date': datetime.date(2020, 3, 4), \n",
    "                                            'province_confirmed': 174, 'province_suspected': None, 'province_cured': 169, 'province_dead': 2} , ignore_index=True)\n",
    "    out_province = out_province.append({'province_name' : '云南省' , 'province_zip_code' : 530000, 'update_time': None, 'update_date': datetime.date(2020, 3, 5), \n",
    "                                            'province_confirmed': 174, 'province_suspected': None, 'province_cured': 169, 'province_dead': 2} , ignore_index=True)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
